DROP PROCEDURE [dbo].[pr3590]
GO
---purpose: Cap nhat lai tinh trang don hang 

CREATE  PROCEDURE [dbo].[pr3590] @DivisionID varchar(20),
				@TranMonth int,
				@TranYear int
AS
Declare @sSQL varchar(8000),
	@Period varchar(20)

Set  @Period =  cast(@TranMonth + @TranYear*100 as varchar(20)) 

Set @sSQL ='--Giu cho SO
Select ''SO'' as TypeID, T01.DivisionID, T00.SOrderID as OrderID, T00.InventoryID, 
	sum(isnull(OrderQuantity, 0)) - sum(isnull(AdjustQuantity,0)) - avg(isnull(ActualQuantity,0))  as RemainQuantity 
From  tb3524 T00 inner join tb3523 T01 on T00.SOrderID = T01.SOrderID 
	left join  (--Giao hang SO
	Select T00.OrderID, InventoryID, sum(ActualQuantity) as ActualQuantity
	From tb0105 T00 inner join tb0104 T01 on T00.VoucherID = T01.VoucherID
	Where KindVoucherID in (2, 4) and isnull(T00.OrderID, '''') <> ''''
	Group by T00.OrderID, InventoryID
	) T02 	on T02.InventoryID = T00.InventoryID and T02.OrderID = T00.SOrderID
Where OrderStatus not in (0, 3,4, 9)  and T01.Disabled = 0 and T01.DivisionID like isnull(''' + @DivisionID + ''', '''')  and 
	T01.TranMonth + T01.TranYear*100 < = ' + @Period+ ' 	
Group by T01.DivisionID,  T00.SOrderID, T00.InventoryID
Union
--Hang dang ve PO
Select ''PO'' as TypeID, T01.DivisionID, T00.POrderID, T00.InventoryID,
	sum(isnull(OrderQuantity, 0)) - sum(isnull(AdjustQuantity,0)) - avg(isnull(ActualQuantity,0))  as RemainQuantity 
From  tb3538 T00 inner join tb3537 T01 on T00.POrderID = T01.POrderID
	left join (--Nhap hang PO
	Select T00.OrderID, InventoryID, sum(ActualQuantity) as ActualQuantity
	From tb0105 T00 inner join tb0104 T01 on T00.VoucherID = T01.VoucherID
	Where KindVoucherID in (1, 5, 7) and isnull(T00.OrderID, '''') <> ''''
	Group by T00.OrderID, InventoryID
	) T03 on T03.OrderID = T00.POrderID and T03.InventoryID = T00.InventoryID
Where OrderStatus not in (0, 3,4, 9)  and T01.Disabled = 0  and T01.DivisionID like isnull(''' + @DivisionID + ''', '''')  and 
	T01.TranMonth + T01.TranYear*100 < = ' + @Period + ' 	
Group by T01.DivisionID, T00.POrderID, T00.InventoryID
Union
--Giu cho du tru ES
Select ''ES'' as TypeID, T01.DivisionID,  T00.EstimateID as OrderID, MaterialID as InventoryID, 
		sum(isnull(MaterialQuantity, 0)) - avg(isnull(ActualQuantity,0))  as RemainQuantity 
From  tb3535 T00 inner join tb3533 T01 on T00.EstimateID = T01.EstimateID
	left join (--Xuat hang ES
	Select T00.OrderID, InventoryID, sum(ActualQuantity) as ActualQuantity
	From tb0105 T00 inner join tb0104 T01 on T00.VoucherID = T01.VoucherID
	Where KindVoucherID in (2, 4) and isnull(T00.OrderID, '''') <> ''''
	Group by T00.DivisionID, T00.OrderID, InventoryID
	) T03 on T03.OrderID = T00.EstimateID and T03.InventoryID = T00.MaterialID 
Where OrderStatus not in (0, 3,4, 9) -- and T01.Disabled = 0 
	 and T01.DivisionID like isnull(''' + @DivisionID + ''', '''')  and 
	T01.TranMonth + T01.TranYear*100 < = ' + @Period  + ' 	
Group by T01.DivisionID, T00.EstimateID, MaterialID'

If exists (SELECT Top 1 1 From sysObjects Where XType = 'V'  and Name = 'vi3620')
	Drop view vi3620
EXEC('Create view vi3620 ---tao boi pr3590	
	as ' + @sSQL)


Set @sSQL = 'Select TypeID, DivisionID, OrderID, 
	sum(case when RemainQuantity <0 then 0 else RemainQuantity end) as RemainQuantity
From vi3620
Group by TypeID, DivisionID, OrderID
Having sum(case when RemainQuantity <0 then 0 else RemainQuantity end) = 0'

If exists (SELECT Top 1 1 From sysObjects Where XType = 'V'  and Name = 'vi3616')
	Drop view vi3616
EXEC('Create view vi3616 ---tao boi pr3590
	as ' + @sSQL)

Update tb3523 Set OrderStatus =  3
From tb3523 T00 inner join vi3616 V00 on T00.SOrderID = V00.OrderID  and V00.TypeID = 'SO' 

Update tb3537 Set OrderStatus =  3
From tb3537 T00 inner join vi3616 V00 on T00.POrderID = V00.OrderID  and V00.TypeID = 'PO' 

Update tb3533 Set OrderStatus =  2
From tb3533 T00 inner join vi3616 V00 on T00.EstimateID = V00.OrderID  and V00.TypeID = 'ES'


GO